from datetime import datetime, timedelta
from time import sleep

import pyodbc
import pandas
from config.excel_config import column_name
from config import file_url_config, query_params_config, jinbo_config
from logs.set_logfile import logger
from utils import file_utils,clean_utils
from clean_data.merge_excel import adjust_column_width
from clean_data.common.common import get_site_dict

# BILL_HEDATE（过账日期）-> 出库时间 0
# 配送商 默认 众志飞救
# GOODNAME 器械名称 -> 配送商备案产品 1
# FACTNAME 生产厂家 -> 生产厂家 2
# CLNTNAME(客户名称) -> 医院名称 3
# XPRIC(基本单价) -> 单价 4
# XCK_QUAT 出货数量 -> 数量 5
# GOO2CODE(批号） -> 批次信息 6


def create_connection():
    try:
        conn = pyodbc.connect(jinbo_config.conn_str)
        # 创建游标
        cursor = conn.cursor()
        return cursor
    except pyodbc.Error as e:
        print(f"数据库操作错误: {e}")
        logger.error(e)
        raise e


def get_jinbo_data(cursor):
    query_start_time = jinbo_config.query_start_time
    date_object = datetime.strptime(jinbo_config.query_end_time, "%Y-%m-%d")
    # 增加一天
    next_day = date_object + timedelta(days=1)
    # 将结果转换为字符串
    query_end_time = next_day.strftime("%Y-%m-%d")
    try:
        sql = (
            "SELECT "
            "t1.Bill_HEDATE, "
            "t4.GOODNAME, "
            "t4.FACTNAME, "
            "t1.CLNTNAME, "
            "t4.XPRIC, "
            "t4.XCK_QUAT, "
            "t4.GOO2CODE, "
             "t4.GOODCODE "
            "FROM "
            "XSCK_H t1 "
            "LEFT JOIN "
            "( "
            "   SELECT "
            "   t2.BILLNONO, "
            "   t2.GOO2CODE, "
            "   t2.XPRIC, "
            "   t2.XCK_QUAT, "
            "   t3.GOODNAME, "
            "   t3.FACTNAME, "
            "   t3.GOODCODE  "
            "   FROM "
            "   XSCK_B t2 "
            "   LEFT JOIN "
            "   INFO_GOOD t3 "
            "   ON "
            "   t2.GOODCODE = t3.GOODCODE "
            ") t4 "
            "ON "
            "t1.BILLNONO = t4.BILLNONO "
            "WHERE "
            f"CONVERT(varchar, t1.Bill_HEDATE, 121) >= '{query_start_time} 00:00:00.000'"
            f"and CONVERT(varchar, t1.Bill_HEDATE, 121) < '{query_end_time} 00:00:00.000'"
            "ORDER BY "
            "t1.Bill_HEDATE DESC;"
        )
        # 执行查询
        cursor.execute(sql)
        # 获取查询结果
        rows = cursor.fetchall()
        return rows
    except Exception as e:
        print(f"sql执行失败: {e}")
        logger.error(e)
        raise e


def close_connection(cursor):
    # 关闭游标
    try:
        cursor.close()
    except Exception as e:
        print(f"数据库关闭失败: {e}")
        logger.error(e)
        raise e


def create_excel(rows):
    ID = []
    CKSJ = []
    PSS = []
    PSS_BACP = []
    SCCJ = []
    YYMC = []
    DJ = []
    SL = []
    PCXX = []
    SJLY = []
    CPBM_PSS = []
    # 众志飞救医疗科技（深圳）有限公司
    for row in rows:
        CKSJ.append(str(row[0])[0:11])
        if row[3] == '广西南宁柳药药业有限公司':
            PSS.append('隆安县中医院')
        else:
            PSS.append('众志飞救医疗科技（深圳）有限公司')
        PSS_BACP.append(row[1])
        SCCJ.append(row[2])
        YYMC.append(row[3])
        DJ.append(row[4])
        SL.append(row[5])
        PCXX.append(row[6])
        CPBM_PSS.append(row[7])
        SJLY.append(jinbo_config.data_source)
    # ID = list(map(lambda i: f'{jinbo_config.id_prefix}{i+1:03d}', range(len(CKSJ))))
    ID = clean_utils.get_ids(jinbo_config.id_prefix, list(map(lambda x: str(x)[0:11], CKSJ)))

    site_dict = get_site_dict()
    yymc_code = []
    exception_msg = []
    for yymc in YYMC:
        if yymc in site_dict:
            yymc_code.append(site_dict[yymc])
            exception_msg.append('')
        else:
            yymc_code.append('')
            exception_msg.append(f'站点信息备案表 没有站点 {yymc}')

    data = {
        column_name['ID']: ID,
        column_name['CKSJ']: CKSJ,
        column_name['PSS']: PSS,
        column_name['CPMC_PSS']: PSS_BACP,
        column_name['CPMC_GSNB']: PSS_BACP,
        column_name['CPBM_PSS']: CPBM_PSS,
        column_name['CPBM_GSNB']: CPBM_PSS,
        column_name['SCCJ']: SCCJ,
        column_name['KHMC_PSS']: YYMC,
        column_name['YYMC_GSNB']: YYMC,
        column_name['KHBM']: yymc_code,
        column_name['YYBM']: yymc_code,
        column_name['DJ']: DJ,
        column_name['SL']: SL,
        column_name['PCXX']: PCXX,
        column_name['LHXX']: ['金博'] * len(SL),
        column_name['SJLY']: SJLY,
        column_name['YC']: [''] * len(PSS)
    }
    df = pandas.DataFrame(data)
    df.to_excel(jinbo_config.excel_file_url, index=False)
    sleep(1)
    adjust_column_width(df=df, file_url=jinbo_config.excel_file_url)


def scraping():
    file_utils.delete_file(jinbo_config.jinbo_error_file_url)
    file_utils.delete_file(jinbo_config.excel_file_url)
    try:
        logger.info("开始抓取金博的数据!")
        logger.info("创建数据库连接")
        cursor = create_connection()
        logger.info("获取数据")
        rows = get_jinbo_data(cursor)
        logger.info("添加进excel表格")
        create_excel(rows)
        logger.info("关闭数据库连接")
        close_connection(cursor)
        logger.info("金博数据获取成功!")
    except Exception as e:
        print(f"金博数据获取失败: {e}")
        logger.error(f"金博数据获取失败: {e}")
        file_utils.delete_file(jinbo_config.excel_file_url)
        file_utils.scraping_error(file_url=jinbo_config.jinbo_error_file_url, log_msg=e)


if __name__ == '__main__':
    # file_utils.create_today_dir()
    scraping()
